﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace Sys.Common
{
    /// <summary>
    /// Nopi电子表格处理
    /// </summary>
    public static class NopiHelper
    {
        /// <summary>
        /// 导出到Excex
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="columns">导出字段</param>
        /// <param name="list">实体列表</param>
        public static void Export<T>(List<string []> columns, List<T> list)
        {
            HSSFWorkbook book = new HSSFWorkbook();

            ISheet sheet = book.CreateSheet("sheet");
            IRow row = null;
            ICell cell = null;

            #region 创建标题

            row = sheet.CreateRow(0);
            row.Height = 500;

            ICellStyle style = book.CreateCellStyle();

            IFont font = book.CreateFont();
            font.Boldweight = 800;
            style.SetFont(font);

            style.Alignment = HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;

            for (int i = 0; i < columns.Count; i++)
            {
                var head = row.CreateCell(i);
                head.CellStyle = style;
                head.SetCellValue(columns[i][1]);
            }

            #endregion

            #region 创建内容

            for (int i = 0; i < list.Count; i++)
            {
                row = sheet.CreateRow(i + 1);
                row.Height = 500;

                for (int j = 0; j < columns.Count; j++)
                {
                    cell = row.CreateCell(j);

                    ICellStyle cellStyle = book.CreateCellStyle();

                    cellStyle.Alignment = HorizontalAlignment.CENTER;
                    cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

                    object drValue = typeof(T).GetProperty(columns[j][0]).GetValue(list[i], null);

                    if (drValue != null)
                    {
                        switch (drValue.GetType().ToString())
                        {
                            case "System.String":
                                cell.SetCellValue(drValue.ToString()); break;
                            case "System.DateTime":
                                IDataFormat format = book.CreateDataFormat();
                                cellStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
                                cell.SetCellValue(Convert.ToDateTime(drValue)); break;
                            case "System.Boolean":
                                cell.SetCellValue(Convert.ToBoolean(drValue)); break;
                            case "System.Int32":
                                cell.SetCellValue(Convert.ToInt32(drValue)); break;
                            case "System.Decimal":
                                cell.SetCellValue(Convert.ToDouble(drValue)); break;
                            case "System.Double":
                                cell.SetCellValue(Convert.ToDouble(drValue)); break;
                            case "System.DBNull":
                                cell.SetCellValue(string.Empty); break;
                            default: cell.SetCellValue(string.Empty); break;
                        }
                    }
                    else { cell.SetCellValue(string.Empty); }

                    cell.CellStyle = cellStyle;
                }
            }

            for (int i = 0; i < columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
                sheet.SetColumnWidth(i, sheet.GetColumnWidth(i) + 500);
            }

            #endregion

            MemoryStream ms = new MemoryStream();
            //写到内存中
            book.Write(ms);

            #region 输出文件

            string filename = StringHelper.GetGuid() + ".xls";

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearHeaders();
            HttpContext.Current.Response.Buffer = false;

            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
            HttpContext.Current.Response.AddHeader("Content-Length", ms.ToArray().Length.ToString());
            HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");

            HttpContext.Current.Response.ContentType = "application/octet-stream";
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());

            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();

            #endregion
        }
    }
}
